<?php

class Application_Model_DbTable_Projects extends Zend_Db_Table_Abstract {

   protected $_name = 'projects';
   protected $_dependentTables = array('Application_Model_DbTable_Tasks');

   public function createProject($name, $description) {
      $data = array(
          'name' => $name,
          'description' => $description,
      );
      $this->insert($data);
   }

   public function updateProject($id, $name, $description, $hide) {
      $data = array(
          'name' => $name,
          'description' => $description,
          'updated_at' => date("Y-m-d h:i:s"),
          'hide' => $hide
      );
      $this->update($data, 'id = ' . $id);
   }

   public function readProject($id) {
      $sql = "SELECT p.id, p.name, p.description, p.created_at,
	(SELECT MIN(t.created_at) FROM tasks t WHERE t.project_id = p.id) as start_date,
	(SELECT MAX(t.updated_at) FROM tasks t WHERE t.project_id = p.id) as end_date
FROM projects p
WHERE p.id = " . $id;
      $row = $this->getDefaultAdapter()->query($sql)->fetchAll();
      if (!$row) {
         throw new Exception("$id not found");
      }
      return $row[0];
   }

   public function deleteProject($id) {
      $this->delete('id = ' . (int) $id);
   }

   public function hideProject($id) {
      $data = array(
          'hide' => 1
      );
      $this->update($data, 'id = ' . $id);
   }

   public function readAll($show_hidden) {
      $sql = "SELECT p.id, p.name, p.description, p.hide,
   (SELECT sum(CASE WHEN status_id == 4 THEN 1 ELSE 0 END)
    FROM tasks
    WHERE project_id = p.id) AS done,
   ( SELECT count(*)
    FROM tasks WHERE project_id = p.id) AS _all
FROM projects p";

      if ($show_hidden == 0) { // show only not hidden
         $sql .= " WHERE p.hide = 0 ";
      } else if ($show_hidden == 2) { // show only hidden
         $sql .= " WHERE p.hide = 1 ";
      }
      $sql .= " ORDER BY p.id DESC ";
      // else show all
      return $this->getDefaultAdapter()->query($sql)->fetchAll();
   }

}
